CREATE table "BANKS" (
    "BANK_ID"    NUMBER,
    "BANK_NAME"  VARCHAR2(256) NOT NULL,
    "BANK_HOMEPAGE" VARCHAR2(1024),
    constraint  "BANKS_PK" primary key ("BANK_ID")
)
/

CREATE sequence "BANKS_SEQ" 
/

CREATE trigger "BI_BANKS"  
  before insert on "BANKS"              
  for each row 
begin  
  if :NEW."BANK_ID" is null then
    select "BANKS_SEQ".nextval into :NEW."BANK_ID" from dual;
  end if;
end;
/




CREATE table "REGIONS" (
    "REGION_ID"   NUMBER,
    "REGION_NAME" VARCHAR2(64) NOT NULL,
    constraint  "REGIONS_PK" primary key ("REGION_ID")
)
/

CREATE sequence "REGIONS_SEQ"
/    

CREATE trigger "BI_REGIONS"  
  before insert on "REGIONS"              
  for each row 
begin  
  if :NEW."REGION_ID" is null then
    select "REGIONS_SEQ".nextval into :NEW."REGION_ID" from dual;
  end if;
end;
/ 



CREATE table "CURRENCIES" (
    "CURRENCY_ID"   NUMBER,
    "CURRENCY_ISO_CODE"   CHAR(3) NOT NULL,
    "CURRENCY_SIGN" VARCHAR2(16),
    "CURRENCY_NAME" VARCHAR2(32) NOT NULL,
    constraint  "CURRENCIES_PK" primary key ("CURRENCY_ID")
)
/


CREATE sequence "CURRENCIES_SEQ"
/    

CREATE trigger "BI_CURRENCIES"  
  before insert on "CURRENCIES"              
  for each row 
begin  
  if :NEW."CURRENCY_ID" is null then
    select "CURRENCIES_SEQ".nextval into :NEW."CURRENCY_ID" from dual;
  end if;
end;
/ 



CREATE table "BANKS_IN_REGIONS" (
    "BANK_REGION_ID" NUMBER,
    "REQUISITES"     VARCHAR2(1024),
    "RATES_PAGE_URL"     VARCHAR2(2048),
    "BANK_ID"        NUMBER NOT NULL,
    "REGION_ID"      NUMBER NOT NULL,
    constraint  "BANKS_IN_REGIONS_PK" primary key ("BANK_REGION_ID")
)
/

CREATE sequence "BANKS_IN_REGIONS_SEQ" 
/

CREATE trigger "BI_BANKS_IN_REGIONS"  
  before insert on "BANKS_IN_REGIONS"              
  for each row 
begin  
  if :NEW."BANK_REGION_ID" is null then
    select "BANKS_IN_REGIONS_SEQ".nextval into :NEW."BANK_REGION_ID" from dual;
  end if;
end;
/   

ALTER TABLE "BANKS_IN_REGIONS" ADD CONSTRAINT "BANKS_IN_REGIONS_FK" 
FOREIGN KEY ("BANK_ID")
REFERENCES "BANKS" ("BANK_ID")
/

ALTER TABLE "BANKS_IN_REGIONS" ADD CONSTRAINT "BANKS_IN_REGIONS_FK2" 
FOREIGN KEY ("REGION_ID")
REFERENCES "REGIONS" ("REGION_ID")
/




CREATE table "BRANCHES" (
    "BRUNCH_ID"      NUMBER,
    "BANK_REGION_ID" NUMBER NOT NULL,
    "ADDRESS"        VARCHAR2(1024),
    "TELEPHONES"     VARCHAR2(256),
    "COORDINATES"    VARCHAR2(64),
    constraint  "BRANCHES_PK" primary key ("BRUNCH_ID")
)
/

CREATE sequence "BRANCHES_SEQ" 
/

CREATE trigger "BI_BRANCHES"  
  before insert on "BRANCHES"              
  for each row 
begin  
  if :NEW."BRUNCH_ID" is null then
    select "BRANCHES_SEQ".nextval into :NEW."BRUNCH_ID" from dual;
  end if;
end;
/   

ALTER TABLE "BRANCHES" ADD CONSTRAINT "BRANCHES_FK" 
FOREIGN KEY ("BANK_REGION_ID")
REFERENCES "BANKS_IN_REGIONS" ("BANK_REGION_ID")
/


CREATE table "TEMPLATES" (
    "TEMPLATE_ID" NUMBER,
    "PURCHASE_TEMPLATE_TEXT" VARCHAR2(2048),
    "SALE_TEMPLATE_TEXT"     VARCHAR2(2048),
    "CURRENCY_ID"            NUMBER NOT NULL,
    "BANK_REGION_ID"         NUMBER NOT NULL,
    constraint  "TEMPLATES_PK" primary key ("TEMPLATE_ID") 
)
/  

CREATE sequence "TEMPLATES_SEQ" 
/

CREATE trigger "BI_TEMPLATES"  
  before insert on "TEMPLATES"              
  for each row 
begin  
  if :NEW."TEMPLATE_ID" is null then
    select "TEMPLATES_SEQ".nextval into :NEW."TEMPLATE_ID" from dual;
  end if;
end;
/

ALTER TABLE "TEMPLATES" ADD CONSTRAINT "TEMPLATES_FK" 
FOREIGN KEY ("CURRENCY_ID")
REFERENCES "CURRENCIES" ("CURRENCY_ID")
/

ALTER TABLE "TEMPLATES" ADD CONSTRAINT "TEMPLATES_FK2" 
FOREIGN KEY ("BANK_REGION_ID")
REFERENCES "BANKS_IN_REGIONS" ("BANK_REGION_ID")
/

ALTER TABLE "TEMPLATES" ADD CONSTRAINT
"TEMPLATES_UNIQUE" UNIQUE ("CURRENCY_ID", "BANK_REGION_ID")
/ 




CREATE table "ERROR_REPORTS" (
    "ERROR_REPORT_ID"    NUMBER,
    "ERROR_REPORT_TEXT"  VARCHAR2(1024) NOT NULL,
    "RAISE_TIME"    DATE NOT NULL,
    "TEMPLATE_ID" NUMBER,
    constraint  "ERROR_REPORTS_PK" primary key ("ERROR_REPORT_ID")
)
/

CREATE sequence "ERROR_REPORTS_SEQ" 
/

CREATE trigger "BI_ERROR_REPORTS"  
  before insert on "ERROR_REPORTS"              
  for each row 
begin  
  if :NEW."ERROR_REPORT_ID" is null then
    select "ERROR_REPORTS_SEQ".nextval into :NEW."ERROR_REPORT_ID" from dual;
  end if;
end;
/

ALTER TABLE "ERROR_REPORTS" ADD CONSTRAINT "ERROR_REPORTS_FK" 
FOREIGN KEY ("TEMPLATE_ID")
REFERENCES "TEMPLATES" ("TEMPLATE_ID")
/



CREATE table "RATES" (
    "BANK_REGION_ID" NUMBER NOT NULL,
    "CURRENCY_ID"    NUMBER NOT NULL,
    "PURCHASE_RATE"  FLOAT,
    "SALE_RATE"      FLOAT,
    "UPDATE_DATE"    DATE NOT NULL
)
/

alter table "RATES" add constraint  "RATES_PK" primary key ("BANK_REGION_ID", "CURRENCY_ID", "UPDATE_DATE")
/

ALTER TABLE "RATES" ADD CONSTRAINT "RATES_FK" 
FOREIGN KEY ("BANK_REGION_ID")
REFERENCES "BANKS_IN_REGIONS" ("BANK_REGION_ID")
/

ALTER TABLE "RATES" ADD CONSTRAINT "RATES_FK2" 
FOREIGN KEY ("CURRENCY_ID")
REFERENCES "CURRENCIES" ("CURRENCY_ID")
/